﻿Imports System.Data.OleDb
Public Class Details
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If IsPostBack = False Then
            If Request.Params("ID") = "" Then Response.Redirect("List.aspx")
            Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionStringList").ConnectionString)
            Dim sqlString As String = "SELECT * from SmartPhoneList_Comments WHERE List_FKID = @f1"
            Dim cmd As New OleDbCommand(sqlString, oleDbConn)
            oleDbConn.Open()
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            Dim dataReader = cmd.ExecuteReader()
            GridView3.DataSource = dataReader
            GridView3.DataBind()

            Dim TagUpdate As String = "SELECT * from SmartPhoneList_Tags WHERE List_FKID = @f1"
            Dim TagUpdatecmd As New OleDbCommand(sqlString, oleDbConn)
            TagUpdatecmd.CommandType = CommandType.Text
            TagUpdatecmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            Dim TagUpdatedataReader = TagUpdatecmd.ExecuteReader()
            'ListView4.DataSource = TagUpdatedataReader
            'ListView4.DataBind()

            Dim DiscussionViewed As String = "INSERT INTO List_View_Times (Author, List_FKID) VALUES (@f1,@f2)"
            Dim ViewCmd As New OleDbCommand(DiscussionViewed, oleDbConn)
            ViewCmd.CommandType = CommandType.Text
            ViewCmd.Parameters.AddWithValue("@f1", User.Identity.Name)
            ViewCmd.Parameters.AddWithValue("@f2", Request.Params("ID"))
            ViewCmd.ExecuteNonQuery()

            Dim AverageRating As String = "SELECT ID,Avg_Rating FROM List_OverallAverage WHERE ID=@f1"
            Dim AverageRatingcmd As OleDbCommand = New OleDbCommand(AverageRating, oleDbConn)
            AverageRatingcmd.CommandType = CommandType.Text
            AverageRatingcmd.Parameters.AddWithValue("@f1", Request.QueryString("ID"))
            Dim dataReader_ratings = AverageRatingcmd.ExecuteReader()
            dataReader_ratings.Read()
            'dataReader.NextResult()
            Try
                If Not IsDBNull(dataReader_ratings("Avg_Rating")) Then lbl_AvgRating.Text = dataReader_ratings("Avg_Rating")
            Catch ex As Exception
                lbl_AvgRating.Text = 0
            End Try

        
        Dim Avg As Single = CInt(lbl_AvgRating.Text)


        If Avg >= 3 Then
            Img_thumbsUp.Visible = True
            Img_thumbsDwn.Visible = False

        Else
            Img_thumbsDwn.Visible = True
            Img_thumbsUp.Visible = False
        End If
        oleDbConn.Close()
        End If
    End Sub

    Protected Sub Btn_Submit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Btn_Submit.Click
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionStringList").ConnectionString)

        'Adding a new comment
        Dim sqlString As String = "INSERT INTO SmartPhoneList_Comments(List_FKID,Author,DateUpdated,Title,Comments) VALUES(@f1,@f2,@f3,@f4,@f5)"
        Dim cmd As New OleDbCommand(sqlString, oleDbConn)
        oleDbConn.Open()
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        cmd.Parameters.AddWithValue("@f3", Convert.ToString(DateAndTime.Now))
        cmd.Parameters.AddWithValue("@f4", Tb_Title.Text)
        cmd.Parameters.AddWithValue("@f5", Tb_Comment.Text)
        cmd.ExecuteNonQuery()


        'Updating the comments gridview to show the newly added comment
        Dim updateComments As String = "SELECT * from SmartPhoneList_Comments WHERE List_FKID = @f1"
        Dim updateCommentsCmd As OleDbCommand = New OleDbCommand(updateComments, oleDbConn)
        updateCommentsCmd.CommandType = CommandType.Text
        updateCommentsCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        Dim dataReader = updateCommentsCmd.ExecuteReader()
        GridView3.DataSource = dataReader
        GridView3.DataBind()
        Tb_Comment.Text = ""
        Tb_Title.Text = ""

        oleDbConn.Close()

    End Sub

   
    Protected Sub Btn_addTag_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Btn_addTag.Click
        Dim listID = Request.Params("ID")
        Dim oleDbConn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionStringList").ConnectionString)
        Dim sqlString As String = "INSERT INTO SmartPhoneList_Tags (List_FKID,Tag_FKID) VALUES(@f1,@f2)"
        oleDbConn.Open()

        'Adding of existing tags to the discussion
        For Each li As ListItem In chkList_Tag.Items
            If (li.Selected = True) Then
                'Here we joing the list and the tag table
                Dim cmd As New OleDbCommand(sqlString, oleDbConn)
                cmd.Parameters.AddWithValue("@f1", listID)
                cmd.Parameters.AddWithValue("@f2", li.Value)
                cmd.ExecuteNonQuery()
            End If
        Next
        'Response.Redirect("Details.aspx?ID=" & Request.Params("ID"))
        If Tb_newTags.Text = "" Then

        Else

            Dim newTags = Tb_newTags.Text.Split(",")
            For Each newTag In newTags
                'first add the new tag to the tag table
                Dim newTagSql As String = "INSERT INTO Tags(Tags) VALUES (@f1)"
                Dim cmd As OleDbCommand = New OleDbCommand(newTagSql, oleDbConn)
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@f1", newTag)
                cmd.ExecuteNonQuery()

                'then find the ID of the new tag
                Dim getLastIDCmd As OleDbCommand = New OleDbCommand("SELECT TOP 1 ID from Tags ORDER by ID DESC", oleDbConn)
                Dim dr = getLastIDCmd.ExecuteReader()
                dr.Read()
                Dim lastID = dr.GetValue(0)

                'Associate te last ID with list ID
                Dim newCmd As OleDbCommand = New OleDbCommand(sqlString, oleDbConn)
                newCmd.Parameters.AddWithValue("@f1", listID)
                newCmd.Parameters.AddWithValue("@f2", lastID)
                newCmd.ExecuteNonQuery()
            Next
        End If
        oleDbConn.Close()
        Response.Redirect("Details.aspx?ID=" & Request.Params("ID"))
    End Sub

    
    Protected Sub btn_Rate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_Rate.Click
        Dim oleDbConn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionStringList").ConnectionString)
        oleDbConn.Open()

        Dim rmvSqlString As String = "DELETE FROM SmartPhoneList_Ratings WHERE Techie = @f1 AND List_FKID = @f2"
        Dim rmvcmd As New OleDbCommand(rmvSqlString, oleDbConn)
        rmvcmd.CommandType = CommandType.Text
        rmvcmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        rmvcmd.Parameters.AddWithValue("@f2", Request.Params("ID"))
        rmvcmd.ExecuteNonQuery()

        Dim insertSqlString As String = "INSERT INTO SmartPhoneList_Ratings (List_FKID,Rating,Techie) VALUES(@f1,@f2,@f3)"
        Dim insertcmd As New OleDbCommand(insertSqlString, oleDbConn)
        insertcmd.CommandType = CommandType.Text
        insertcmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        insertcmd.Parameters.AddWithValue("@f2", Rating1.CurrentRating)
        insertcmd.Parameters.AddWithValue("@f3", User.Identity.Name)
        insertcmd.ExecuteNonQuery()
        oleDbConn.Close()
        Rate_Value.Text = "You have rated: " & Rating1.CurrentRating

    End Sub
End Class